package com.xky.ui.module.warehouseManagement.select;

import com.xky.pojo.Warehouses;
import com.xky.utils.DBUtils;
import lombok.Getter;
import lombok.Setter;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Setter
@Getter
public class WarehouseService {

    /**
     * 从结果集创建仓库对象
     * @param resultSet 结果集
     * @return 仓库对象
     * @throws SQLException SQL异常
     */
    private static Warehouses createWarehouseFromResultSet(ResultSet resultSet) throws SQLException {
        Warehouses warehouse = new Warehouses();
        warehouse.setWarehouseId(resultSet.getInt("warehouse_id"));
        warehouse.setWarehouseCode(resultSet.getString("warehouse_code"));
        warehouse.setWarehouseName(resultSet.getString("warehouse_name"));
        warehouse.setLocation(resultSet.getString("location"));
        warehouse.setIsActive(resultSet.getBoolean("is_active"));
        warehouse.setCreatedTime(resultSet.getTimestamp("created_time").toLocalDateTime());
        warehouse.setUpdatedTime(resultSet.getTimestamp("updated_time").toLocalDateTime());
        return warehouse;
    }

    /**
     * 查询所有仓库
     * @return 仓库列表
     */
    public static List<Warehouses> selectAllWarehouses(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Warehouses> warehousesList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement("SELECT * FROM warehouses ORDER BY warehouse_id");
            resultSet = preparedStatement.executeQuery();
            
            // 直接使用while循环处理结果集
            while (resultSet.next()){
                warehousesList.add(createWarehouseFromResultSet(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return warehousesList;
    }


    /**
     * 根据ID查询仓库
     * @param id 仓库ID
     * @return 仓库列表
     */
    public static List<Warehouses> selectWarehousesById(Integer id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Warehouses> warehousesList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement("SELECT * FROM warehouses WHERE warehouse_id = ?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                warehousesList.add(createWarehouseFromResultSet(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return warehousesList;
    }

    /**
     * 根据仓库名称查询
     * @param name 仓库名称
     * @return 仓库列表
     */
    public static List<Warehouses> searchWarehousesByName(String name) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Warehouses> warehousesList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(
                "SELECT * FROM warehouses WHERE warehouse_name LIKE ? ORDER BY warehouse_id"
            );
            preparedStatement.setString(1, "%" + name + "%");
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                warehousesList.add(createWarehouseFromResultSet(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return warehousesList;
    }

    /**
     * 根据仓库编码查询
     * @param code 仓库编码
     * @return 仓库列表
     */
    public static List<Warehouses> searchWarehousesByCode(String code) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Warehouses> warehousesList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(
                "SELECT * FROM warehouses WHERE warehouse_code LIKE ? ORDER BY warehouse_id"
            );
            preparedStatement.setString(1, "%" + code + "%");
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                warehousesList.add(createWarehouseFromResultSet(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return warehousesList;
    }

    /**
     * 根据仓库位置查询
     * @param location 仓库位置
     * @return 仓库列表
     */
    public static List<Warehouses> searchWarehousesByLocation(String location) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Warehouses> warehousesList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(
                "SELECT * FROM warehouses WHERE location LIKE ? ORDER BY warehouse_id"
            );
            preparedStatement.setString(1, "%" + location + "%");
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                warehousesList.add(createWarehouseFromResultSet(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return warehousesList;
    }

    /**
     * 根据仓库名称、编码或位置进行模糊查询
     * @param keyword 关键词
     * @return 仓库列表
     */
    public static List<Warehouses> searchWarehousesByKeyword(String keyword) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Warehouses> warehousesList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            // 使用LIKE进行模糊查询，支持仓库名称、编码和位置
            preparedStatement = connection.prepareStatement(
                "SELECT * FROM warehouses WHERE warehouse_name LIKE ? OR warehouse_code LIKE ? OR location LIKE ? ORDER BY warehouse_id"
            );
            String searchPattern = "%" + keyword + "%";
            preparedStatement.setString(1, searchPattern);
            preparedStatement.setString(2, searchPattern);
            preparedStatement.setString(3, searchPattern);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                warehousesList.add(createWarehouseFromResultSet(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return warehousesList;
    }
} 